#!/usr/bin/env python
#-*- encoding:UTF-8 -*-
"""
Connect to MS Access DB
"""

import unittest
import win32com.client
import adodbapi, pyodbc


# Connect to Oracle;
#connStrOC = u"Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocdb1.grs-cro.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ocp)));User Id=SASEXTRACT;Password=SASEXTRACT;"
#connOC = adodbapi.connect(connStrOC)

# Connect to Access;
mdbfile = r'D:\SMARTER\wxPythonApp\meta_lib.mdb'
mdbfile = r'D:\wxPythonApp\meta_lib.mdb'
mdbfile = r'D:\SMARTER\pysds\meta_lib.mdb'
connStr = u'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s' % mdbfile
#connStr = u'Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % mdbfile
conn = adodbapi.connect(connStr)
conn = pyodbc.connect(connStr)

cursor = conn.cursor()
cursor.execute("select STUDYID,DATASET,DESCRIPTION,CLASS,STRUCTURE,PURPOSE,KEYVAR,COMMENTS,VERSION from SDTM_DATASETS")
rs = cursor.fetchall()

colnames = cursor.description

headers = [(colname[0],colname[3]) for colname in colnames]
rows = [_rows for _rows in rs]
firstcol = [_col[0] for _col in rs]

for index,col in enumerate(colnames):
    print index, "==", col[0]
    
for index,row in enumerate(rows):
    print index, "==", rows
    
# close connection
conn.close()


 